#!/usr/bin/env python3 
# -*- coding: utf-8 -*- 
# @Author : Leo
# @File : mysqltool.py

import time
import pymysql
from TianjinGovScrapy import settings

MYSQL_HOST = settings.MYSQL_HOST
MYSQL_USER = settings.MYSQL_USER
MYSQL_PASSWORD = settings.MYSQL_PASSWORD
MYSQL_PORT = settings.MYSQL_PORT
MYSQL_DB = settings.MYSQL_DB

conn = pymysql.connect(host=MYSQL_HOST, user=MYSQL_USER, passwd=MYSQL_PASSWORD,
                       db=MYSQL_DB, charset='utf8mb4', port=MYSQL_PORT)
cursor = conn.cursor()


class Sqltool:

    def __init__(self):
        status = self.createDBTable()

    @classmethod
    def createDBTable(cls):
        Sql_city_table = '''
            CREATE TABLE IF NOT EXISTS `city_proc_scrapy` (
            `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
            `title` VARCHAR(200) NOT NULL DEFAULT '0',
            `news_id` CHAR(50) NOT NULL DEFAULT '0',
            `create_time` DATE NULL DEFAULT NULL,
            `public_time` DATE NULL DEFAULT NULL,
            `url` VARCHAR(100) NULL DEFAULT NULL,
            `content` TEXT NULL,
            PRIMARY KEY (`id`)
        )
        COLLATE='utf8_general_ci'
        ENGINE=InnoDB
        ;
        '''
        Sql_district_table = '''
            CREATE TABLE IF NOT EXISTS `district_proc_scrapy` (
            `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
            `title` VARCHAR(200) NOT NULL DEFAULT '0',
            `news_id` CHAR(50) NOT NULL DEFAULT '0',
            `create_time` DATE NULL DEFAULT NULL,
            `public_time` DATE NULL DEFAULT NULL,
            `url` VARCHAR(100) NULL DEFAULT NULL,
            `content` TEXT NULL,
            PRIMARY KEY (`id`)
        )
        COLLATE='utf8_general_ci'
        ENGINE=InnoDB
        ;'''
        cursor.execute(Sql_city_table)
        cursor.execute(Sql_district_table)
        conn.commit()

    @classmethod
    def insert2db(cls, news_id, news_title, news_date, news_url, news_content, area_id):
        if area_id == '1665':  # 市级
            Sql = 'INSERT IGNORE INTO city_proc_scrapy(title, news_id, create_time, public_time, url, content) ' \
                  'VALUES("{title}", "{news_id}", "{create_time}","{public_time}", "{url}", "{content}")'
            Sql = Sql.format(title=news_title.replace('\"', '\"\"'), news_id=news_id,
                             create_time=cls.getDate(), public_time=news_date,
                             url=news_url, content=news_content.replace('\"', '\"\"'))
            cursor.execute(Sql)
        elif area_id == '1664':  # 区级
            Sql = 'INSERT IGNORE INTO district_proc_scrapy(title, news_id, create_time, public_time, url, content) ' \
                  'VALUES("{title}", "{news_id}", "{create_time}","{public_time}", "{url}", "{content}")'
            Sql = Sql.format(title=news_title.replace('\"', '\"\"'), news_id=news_id,
                             create_time=cls.getDate(), public_time=news_date,
                             url=news_url, content=news_content.replace('\"', '\"\"'))
            cursor.execute(Sql)
        conn.commit()

    @staticmethod
    def getDate():
        return time.strftime('%Y-%m-%d')
